CREATE proc asi_NextSequenceValueOut
  @lastValue int OUT,
  @sequenceName nvarchar(30),
  @userKey uniqueidentifier,
  @systemEntityKey uniqueidentifier = '00000000-0000-0000-0000-000000000000',
  @increment int = 1,
  @startValue int = 0
AS

--declare @lastValue int

--Check to make sure the counter exists - create it if not
if not exists(select 1 from SequenceCounter where CounterName = @sequenceName and SystemEntityKey = @systemEntityKey)
   insert SequenceCounter (SystemEntityKey, CounterName, CurrentValue, UpdatedOn, UpdatedByUserKey, CreatedOn, CreatedByUserKey)
   values (@systemEntityKey, @sequenceName, @startValue, getdate(), @userKey, getdate(), @userKey)

--Increment the current value
update SequenceCounter
   set @lastValue = CurrentValue = (CurrentValue + @increment),
       UpdatedOn = getdate(),
       UpdatedByUserKey = @userKey
   where CounterName = @sequenceName
       and SystemEntityKey = @systemEntityKey

-- Counter Filtering / 666 handler
if exists (select 1 from SystemConfig where ParameterName = 'EnableCounterFiltering' and ParameterValue = 'True')
    begin
    declare @lastValueStr varchar(11)
    declare @exponent tinyint
    declare @stringLen tinyint
    declare @stringPos tinyint

    select @lastValueStr = convert (varchar(11), @lastValue)
    select @stringLen = len (@lastValueStr)

    processString:
    select @stringPos = charindex ('666', @lastValueStr)
    if @stringPos <> 0
        begin
            select @lastValue = @lastValue + power (10, (@stringLen - @stringPos - 2))
            select @lastValueStr = convert (varchar(11), @lastValue)

            update SequenceCounter
                set CurrentValue = @lastValue,
                    UpdatedOn = getdate(),
                   UpdatedByUserKey = @userKey
             where CounterName = @sequenceName    
                     and SystemEntityKey = @systemEntityKey

            goto processString
        end

    end -- if exists (select 1 from SystemConfig...

--Return the last value of the assigned counter range
--select @lastValue CurrentValue

GO